{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "localhost = \"192.168.90.193\"\n",
    "localuser = \"gmgbj\"\n",
    "localpwd = 'G7gIVYB9Xk^7'\n",
    "localurl='mysql+pymysql://gmgbj:G7gIVYB9Xk^7@192.168.90.193:3306/Strategy?charset=utf8'\n",
    "bjurl = 'mysql+pymysql://gmgbj:G7gIVYB9Xk^7@192.168.3.144:3306/Strategy?charset=utf8'\n",
    "    \n",
    "xuyi_aliyun = \"121.40.53.82\"\n",
    "xy_user = \"root\"\n",
    "xy_pwd = \"Fangzhouqwer1069.\"\n",
    "totalurl='mysql+pymysql://root:Fangzhouqwer1069.@121.40.53.82:3306/Strategy?charset=utf8'\n",
    "aliyun= 'mysql+pymysql://dbuser:dbuser@121@rm-bp1witqzl76lpag957o.mysql.rds.aliyuncs.com:3306/strategy?charset=utf8'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(columns=['signalid', 'strategyid','market', 'symbol', 'klinetype', 'bstype','triggerprice','triggertime', 'issend',\n",
    "                           'confidence', 'isused', 'isright', 'addtime' ])\n",
    "msg = df.to_sql(\n",
    "            name=\"signalinfo\",\n",
    "            con=localurl,\n",
    "            if_exists='replace',  #'fail'，'replace'，'append'}，默认'fail' \n",
    "          \n",
    "dtype={'signalid':sqlalchemy.types.INTEGER(),\n",
    "       'strategyid':sqlalchemy.types.INTEGER(),\n",
    "       'market':sqlalchemy.types.NVARCHAR(length=2),\n",
    "       'symbol':sqlalchemy.types.NVARCHAR(length=10),\n",
    "       'bstype':sqlalchemy.types.NVARCHAR(length=1),\n",
    "       'triggerPrice':sqlalchemy.types.DECIMAL(10,4),\n",
    "       'triggertime': sqlalchemy.types.DATETIME(),\n",
    "       'klinetype':sqlalchemy.types.VARCHAR(5),\n",
    "       'issend':sqlalchemy.types.BOOLEAN(),\n",
    "       'confidence':sqlalchemy.types.BOOLEAN(),\n",
    "       'isused':sqlalchemy.types.BOOLEAN(),\n",
    "       'isright':sqlalchemy.types.BOOLEAN(),\n",
    "     'addtime': sqlalchemy.types.DATETIME()},\n",
    "  index_label='signalid',\n",
    "            index=False)\n",
    "print(msg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns=['signalID', 'strategyID','market', 'symbol', 'bstype', 'isSendDingding','confidence', 'isUse', 'isRight', 'addTime' ])\n",
    "df['market'] = 'HK'\n",
    "df['symboll'] = 'HSI'\n",
    "msg = df.to_sql(\n",
    "            name=\"signalinfo\",\n",
    "            con=localurl,\n",
    "            if_exists='append',  #'fail'，'replace'，'append'}，默认'fail' \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>symbol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>00968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>06098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>00868</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>02331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>03968</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    symbol\n",
       "0   symbol\n",
       "1    00001\n",
       "2    00002\n",
       "3    00003\n",
       "4    00005\n",
       "..     ...\n",
       "56   00968\n",
       "57   06098\n",
       "58   00868\n",
       "59   02331\n",
       "60   03968\n",
       "\n",
       "[61 rows x 1 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_component = pd.read_csv('F:\\HQData\\HK\\HSI_COMPONENT_202109.csv', header=None, names=['symbol'], encoding='utf8')\n",
    "# df_component['symbol'] = df_component['symbol'].apply(lambda x: str(x).rjust(5, '0'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_component.to_csv('F:\\HQData\\HK\\HSI_COMPONENT_202109.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_component['index'] = 'HSI'\n",
    "df_component.to_sql( name=\"component\",\n",
    "            con=bjurl,\n",
    "            if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
